Consumer Credit Card Spend Analysis
I worked on the Consumer's Credit Card Spend Project. I will be diving into the background, my full process of cleaning, analyzing and visualizing the data, along with my final suggestions and summary of the data.
Tools Used: Microsoft Excel | MySQL | Power BIQuick Links: Power BI Dashboard | GitHub Repository
Below is a table of contents in case you want to go to any section.
Table Contents:
- Introduction
- Data Dictionary
- Process
- Finished Project
- Overview of Data
- Insights
- Recommendations
- What I Learned
- Resources
INTRODUCTION
This is a fictional dataset with details of sample 100 customers using different types of credit cards living in different cities, working different jobs in different companies.
Overall Goal: “Analyze the Consumer's credit card spending data and generate insights”
Questions: What is the average age of credit card holders?
What is the average credit limit?
Which is the most commonly held credit card type?
Which is the most common spending category?
What is the rate of late-paying customers? etc..
DATA DICTIONARY
Entity Relationship Diagram
Customer: A person that hold the credit card.1. Customer
Table Schema
Sample Data
Repayment: This table holds the data of repayment date and amount on which the credit ccard bill was paid.2. Repayment
Table Schema
Sample Data
Spend: This table holds the data about the product type, date and amount on which the credit card was used.3. Spend
Table Schema
Sample Data
Process
Overview: First I cleaned the data in Excel then I used MySQL Workbench (GUI of MySQL) to create a database and find the results of the queries. Then I used Power BI to create a dashboard to showcase all the metrics and analyze the data.
Microsoft Excel
I started downloading the data from Kaggle and checked if there was any data that has to be cleaned.
The data was already clean but the company column had few incorrect values- the customers whose job category was self employed had a name of company in company column so I replaced those values with "self employed".
MySQL
First I created the database using ERD and exported all the CSV files to respective tables at the same time I familiarized myself with the data. Then connected the tables using Primary and Foreign Key constraints. And then used my quering skills to find answers.
Q1. What is the average credit limit?
view query
Q2. Which type of credit card is most commonly held by customers?
view query
Q3. What is the average age of credit card holders?
view query
Q4. What is the most common spending category?
view query
Q5. Show the month-wise spend across the years in descending order.
view query
Q6. What is the average spend per category?
view query
Q7. What is the average no. of transactions per month?
view query
Q8. List the top 5 cities with the highest amount spent along with their no. of transactions.
view query
Q9. List the card types and the amount spent with them over the years.
view query
Q10. Which is the most commonly used credit card type?
view query
Q11. What is the average no. of days a customer pays off their credit card bill?
view query
Q12. What is the rate of late-paying customers, assume the no. of days to pay off the bill is 30 days.
view query
Q13. Show the customer base city-wise in descending order.
view query
Q14. What is the spending range of each customer?
view query
Q15.
view query
Power BI
I used Microsoft Power BI to showcase my analysis and metrics in a dashboard.
To view my complete dashboard click here.
I created a new table called late_payment to summarize every customers' spending and repaying date to calculate the no. of days they took to pay their credit card bills.
I created four columns -
• customer_id - to store customers' id.
• spend_date - to store the last spending date.
• repayment_date - to store the last bill payment date.
• days - to store the no. of days they took to pay their bill.
Also I created a measure %_late_payment to calculate the rate of late paying customer considering the maximun no. of days to pay the bill is 30 days.
MISC.
Microsoft Word
Notes - Notes for the project including the answer of queries, what I was looking for, and anything else that has to do with the project.I used NovyPro to host the dashboard.
FINISHED PROJECT
Here is my finished project: Consumer Credit Card Spend Dashboard. You can view the links to my SQL code on GitHub used for analysis here.
Preview
Overview of Data
Data:
- Average Amount Spent by Product Type
- Transactions by Product Type
- Customer base by City
- Customer base & Amount Spent by Card Type
- Customers by Job Segment
- MoM Transactions & Average Amount Spent
- Total Amount & Transactions by City
- KPIs
Average Amount Spent by Product Type
The top 5 product categories by average amount spent are:- Clothes: ₹259,490
- Camera: ₹259,260
- Movie ticket: ₹258,480
- Train ticket: ₹250,510
- Shopping: ₹249,650
Transactions by Product Type
The top 5 products by transactions are:- Petrol: 200
- Camera: 160
- Food: 160
- Air Ticket: 147
- Train Ticket: 132
Customer base by City
The top 5 cities with highest customer base are Cochin, Bangalore, Calcutta, Mumbai and Chennai.Customer base & Amount Spent by Card Type
Customer base and amount spent by card type are:- Gold: 38%, ₹146M
- Platinum: 32%, ₹141M
- Silver: 30%, ₹77M
Customer base and Amount Spent by Job Segment
Customer base and Amount Spent by Job Segment are:- Govt: 29%, ₹63M
- Self Employed: 23%, ₹66M
- Normal Salaried: 22%, ₹107M
- Salaried in MNC: 13%, ₹58M
- Salaried in Pvt: 13%, ₹68M
MoM Transactions & Average Amount Spent
- The biggest jump in average amount spent occurs between December and January. This suggests that there is a significant opportunity for businesses to target consumers during the holiday season.
- The biggest drop in average amount spent occurs between August and October. This suggests that businesses may need to adjust their marketing and sales strategies during the monsoon months.
- The average amount spent per transaction has been increasing over time. This is likely due to a number of factors, such as inflation, rising incomes, and changes in consumer spending habits.
Total Amount & Transactions by City
The size of the bubble is proportional to the amount spent in that city.Top cities for spending and transactions are:
- Cochin: ₹73M, 298
- Bangalore: ₹69M, 299
- Calcutta: ₹64M, 251
- Mumbai: ₹50M, 215
- Chennai: ₹32M, 130
KPIs
Total amount spent: The total amount spent on consumer credit cards in the dataset is ₹365 million.Transactions: There were a total of 1500 transactions.
Average credit limit: The average credit limit is ₹285,000.
Average age of cardholders: The average age of cardholders is 47.
Late payment rate: The late payment rate is 46%.
INSIGHTS
Below are the general insights:
- The average credit limit is ₹285K. This is a relatively high credit limit, which could lead to overspending and debt problems for some consumers.
- The average age of consumers is 47 years old. This is slightly older than the median age of the Indian population, which is 28.4 years old.
- 46% of consumers have made a late payment in the past year. This is a high percentage, and it indicates that many consumers are struggling to manage their debt.
- The top product categories for spending are clothes, cameras, movie tickets, train tickets, and shopping. This suggests that consumers are spending more money on discretionary items than on essential items.
- The top cities for spending are Cochin, Bangalore, Calcutta, Mumbai, and Chennai. These are all major metropolitan areas with large populations.
- The top job segments for spending are government, self-employed, normal salaried employee, and salaried employee. This suggests that consumers from all walks of life are spending money on credit cards.
RECOMMENDATIONS
- Target customers with high credit limits and low late payment rates for special offers and promotions.
- Develop loyalty programs and rewards programs to encourage customers to spend more on their credit cards.
- Partner with merchants in popular product categories, such as clothes, cameras, and movie tickets, to offer exclusive discounts and promotions to credit card holders.
- Offer targeted marketing campaigns to customers in different cities, based on their spending habits.
- Provide financial literacy education to customers to help them manage their credit card debt effectively.
WHAT I LEARNED
This was the first project where I went through the whole data analysis process of data cleanining, manpulations, analysis and visualization.
Throughout the process I have learnt a lot, some of it are:
- Using date functions in sql.
- Creating tables using DAX queries.
- Using DAX functions like summarize(), maxx()- max function with filter and datediff()- to find difference between dates.
RESOURCES
This dataset was downloaded from Kaggle, to download it click here.